<#assign StringUtils = beansWrapperFn.getStaticModels()["org.apache.commons.lang3.StringUtils"]>
WITH W_ROLE_PERMISSION AS
  (SELECT T.*,
  ROW_NUMBER() OVER(ORDER BY T_ROLE.ORDER_NUM, T_ROLE.CODE, T_DICTIONARY.ORDER_NUM, T_DICTIONARY.CODE, T_PERMISSION.CODE) RN ,
  T_ROLE.CODE          AS ROLE_CODE,
  T_ROLE.NAME          AS ROLE_NAME,
  T_PERMISSION.CODE    AS PERMISSION_CODE,
  T_PERMISSION.NAME    AS PERMISSION_NAME,
  T_PERMISSION.TYPE_ID AS PERMISSION_TYPE_ID,
  T_DICTIONARY.CODE    AS PERMISSION_TYPE_CODE,
  T_DICTIONARY.NAME    AS PERMISSION_TYPE_NAME
FROM C1_ROLE_PERMISSION T
LEFT JOIN C1_ROLE T_ROLE
ON (T_ROLE.ID = T.ROLE_ID)
LEFT JOIN C1_PERMISSION T_PERMISSION
ON (T_PERMISSION.ID = T.PERMISSION_ID)
LEFT JOIN C1_DICTIONARY T_DICTIONARY
ON (T_DICTIONARY.ID = T_PERMISSION.TYPE_ID)
WHERE 1 = 1
<#if StringUtils.isNotBlank(roleCode)>
  AND (instr(T_ROLE.code, :roleCode) > 0 OR instr(T_ROLE.name, :roleCode)   > 0)
</#if>
<#if StringUtils.isNotBlank(permissionCode)>
	and (instr(T_PERMISSION.code, :permissionCode) > 0 or instr(T_PERMISSION.name, :permissionCode) > 0)
</#if>
<#if StringUtils.isNotBlank(permissionTypeCode)>
	and (instr(T_DICTIONARY.code, :permissionTypeCode) > 0 or instr(T_DICTIONARY.name, :permissionTypeCode) > 0)
</#if>
  )
SELECT T.*
    FROM W_ROLE_PERMISSION T
WHERE t.rn BETWEEN <#if (begin_row_num??)>:begin_row_num<#else>1</#if> AND <#if (end_row_num??)>:end_row_num<#else>20</#if>